/*
 * Created on 13-May-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.trade.fixation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;

import pc.sql.value.*;

import pc.system.Record;
import pc.system.Tasks;
import pc.util.Utils;
import wstrader.task.GenReport;
import wstrader.task.trade.TradeTask;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Contract extends TradeTask 
{
	  public Contract(Tasks owner)
	  {
	    super(owner);
	  }
	  
	  public long doTask()
	  {
	  	long FixRet;
	  	long Ret;
	    String table_name = "contract_price_fixation";
	    int task_id     = this.getRequest().getIntValue("task_id");
	  
	    long Pcnt_Cnt_Id =this.getRequest().getLongValue("contracts");
	    long focused_id = this.getRequest().getLongValue("id");
	    int FixType = this.getRequest().getIntValue("fixation_type");
	    String Type = this.getRequest().getStringValue("type");
	    if (Pcnt_Cnt_Id == 0)
	    {
	    	Pcnt_Cnt_Id =this.getRequest().getLongValue("fetch_id");
	    }
	    
	    switch (task_id)
		{
	        
	      case 1 : // Update
	      	
	      	if (focused_id > 0)
	      	{
	      		FixRet = doUpdate(table_name,focused_id);
	        
	      		float sumValue = getSum("tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		//float sumDiff = (this.getRequest().getFloatValue("tottons"))-sumValue;
	      		//if (sumDiff < 10) sumValue = this.getRequest().getFloatValue("tottons");
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"fixed_tons"," "+sumValue,"contracts");
	      		Ret = setCalculateFieldValue(Pcnt_Cnt_Id,"unfixed_tons","tons","fixed_tons","-","contracts");
	    	
	      		float sum_tons_fixed = getSum("tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"fixed_tons"," "+sum_tons_fixed,"contracts");
	      		float sum_hedge_price = getSum("hedge_price * tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		float hedge_price = 0;
	      		if (sum_tons_fixed > 0)
	      		{
	      			hedge_price = (sum_hedge_price/sum_tons_fixed);
	      		}
	      		float sum_price = getSum("price_foreign * tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		float price =0;
	      		if (sum_tons_fixed >0)
	      		{
	      			price = (sum_price/sum_tons_fixed);
	      		}
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"hedge_price"," "+hedge_price,"contracts");
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"contract_price_foreign"," "+price,"contracts");

	      		updatedPosRptField(Pcnt_Cnt_Id);
	      		
	      		if(FixType>0)
	      		{
	      			updateFuturesContractRecord(FixRet,Type);
	      		}
	      	}
	        break;
	      case 2 : // Delete
	      	System.out.println("focused_id:=" + focused_id);
	      	if (focused_id > 0)
	      	{
	      		FixRet = doDelete(table_name,focused_id);
	        
	      		float sumValue = getSum("tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"fixed_tons"," "+sumValue,"contracts");
	      		Ret = setCalculateFieldValue(Pcnt_Cnt_Id,"unfixed_tons","tons","fixed_tons","-","contracts");
    	
	      		float sum_tons_fixed = getSum("tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"fixed_tons"," "+sum_tons_fixed,"contracts");
	      		float sum_hedge_price = getSum("hedge_price * tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		float hedge_price = 0;
	      		if (sum_tons_fixed > 0)
	      		{
	      			hedge_price = (sum_hedge_price/sum_tons_fixed);
	      		}
	      		float sum_price = getSum("price_foreign * tons_fixed","contract_id","contract_price_fixation",Pcnt_Cnt_Id);
	      		float price =0;
	      		if (sum_tons_fixed >0)
	      		{
	      			price = (sum_price/sum_tons_fixed);
	      		}
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"hedge_price"," "+hedge_price,"contracts");
	      		Ret = setFieldValue(Pcnt_Cnt_Id,"contract_price_foreign"," "+price,"contracts");

	      		updatedPosRptField(Pcnt_Cnt_Id);
	      		
	      		deleteFuturesContractRecord(focused_id);
	      	}
	      	break;
	      case 3: //update rollover
	      	if (Pcnt_Cnt_Id > 0)
	      	{
		    	int RollingId = getRequest().getIntValue("roll_id");
		    	if (RollingId > 0) updateRollContract(Pcnt_Cnt_Id);	      		
	      	}
	      	break;
	      case 4: //update rollover
		      	if ((getUser().getComanyName().toUpperCase()).compareTo("MERCAFE VIETNAM")==0)
   		      	{
			      	getResponse().setReport(new PrintMercafeAmendedContract(this).generate());		      		
   		      	}
	      	break;	      	
	      case 5: //Report
  		     //if (Type.compareTo("P")==0) getResponse().setReport(new PrintPurFixContract(this).generate());
   		     //if (Type.compareTo("S")==0) getResponse().setReport(new PrintSalFixContract(this).generate());
	      		getResponse().setReport(new PrintSalFixContract(this).generate());
	        break;        
	        
	      default : //View only
	        break;
	    }
	    this.setFocusedID(Pcnt_Cnt_Id);

	    return Pcnt_Cnt_Id;
	  }	  
	  
	  public ResultSet fetchUnfixedContractList()
	  {
	  	int BuyerSellerId = getRequest().getIntValue("buyer_seller");
	  	String TerminalMarket = getRequest().getStringValue("terminal_market");
	  	java.sql.Date TerMth = getRequest().getValueDate("ter_mth");
	  	String Type = getRequest().getStringValue("cnt_type");
	  	
	  	if (TerminalMarket.compareTo("")==0) TerminalMarket ="0";
	  	if (Type.compareTo("")==0) Type ="0";
	  	String Sql ="Select * from contracts where " +
	  				"(supplier_buyer_id = ? OR ?) " +			
					"and (terminal_market = ? OR ?) " +
					"and (terminal_month = ? OR ?) " +
					"and (type = ? OR ?) " +
					"and (contract_price_type = ?) order by contracts.id desc";
	  	
	    Vector vp = new Vector();

	    vp.addElement(new IntValue(BuyerSellerId));
	    vp.addElement(new BooleanValue(BuyerSellerId == 0));

	    vp.addElement(new StringValue(TerminalMarket));
	    vp.addElement(new BooleanValue(TerminalMarket.compareTo("0") == 0));

	    vp.addElement(new DateValue(TerMth));
	    vp.addElement(new BooleanValue(TerMth.compareTo(new java.sql.Date(0)) == 0));
	  	
	    vp.addElement(new StringValue(Type));
	    vp.addElement(new BooleanValue(Type.compareTo("0") == 0));
	    
	    vp.addElement(new StringValue("U"));
	    
	  	return getSQLCommand().executeQuery(Sql,vp);
	  }
	  
	  public ResultSet fetchFixedList()
	  {
	  	int PcntCntNo = getRequest().getIntValue("contracts");
	    if (PcntCntNo == 0)
	    {
	    	PcntCntNo =this.getRequest().getIntValue("fetch_id");
	    }  	
	  	String Sql = "Select * from contract_price_fixation where contract_id = " + PcntCntNo + " order by id";
	  	return getSQLCommand().executeQuery(Sql);
	  }

	  public ResultSet fetchRollList()
	  {
	  	int PcntCntNo = getRequest().getIntValue("contracts");
	    if (PcntCntNo == 0)
	    {
	    	PcntCntNo =this.getRequest().getIntValue("fetch_id");
	    }  	
	  	String Sql = "Select * from contract_roll_over where contract_id = " + PcntCntNo + " order by id";
	  	return getSQLCommand().executeQuery(Sql);
	  }
	  
	  public String getNew_CntFixRef()
	  {
	    String sql =
	        "SELECT MAX(ref_number) as ref_number FROM contract_price_fixation ";
	    Record rc = new Record(this.getSQLCommand().executeQuery(sql));
	    String ref = (!rc.isEmpty()) ? rc.getString("ref_number") : "CF 00-00000";
	    rc.close();
	    return "CF" + " " + this.getNextRefNumber(ref);
	  } 
	  
	  
	  private void updateFuturesContractRecord(long id,String CType)
	  {
	  	String RefNumber = getRequest().getStringValue("fut_ref_no");
	  	java.sql.Date TransactionDate= Utils.strToDate(getRequest().getStringValue("fixation_date"));
	  	String FType ="";
	  	double Tons=0;
	  	int Lots=0;
	  	long RecExist=0;
	  	if (CType.compareTo("S")==0)
	  	{
	  		Tons = getRequest().getDoubleValue("tons_fixed");
	  		Lots = getRequest().getIntValue("lots_fixed");
	  		RecExist = findID("futures_contract","fixation_id","bot_sold",id,"B");
	  		FType = "B";
	  	}
	  	else
	  	{
	  		Tons = (getRequest().getDoubleValue("tons_fixed"));
	  		Lots = (getRequest().getIntValue("lots_fixed"));
	  		RecExist = findID("futures_contract","fixation_id","bot_sold",id,"S");
	  		FType = "S";
	  	}
	  	
	  	float HedgePrice = getRequest().getFloatValue("hedge_price");
	  	float Differential = getRequest().getFloatValue("differential");
	  	float Price = getRequest().getFloatValue("price_foreign");
	  	long FixationId = id;
	  	java.sql.Date TerminalMth = Utils.strToDate(getRequest().getStringValue("terminal_month"));
	  	String TerminalMarket = getRequest().getStringValue("terminal_market");
	  	int BrokerId=getRequest().getIntValue("fixation_type");
	  	int OrdType=6;
	  	double i = 17.01;
	  	if (TerminalMarket.compareTo("LIFFE")==0)
	  	{
	  		Tons = Lots * 5;
	  	}
	  	else
	  	{
	  		Tons = Lots * i ;
	  	}
	  	String SqlStr = "";
	  	if (RecExist==0)
	  	{
	  		SqlStr =
			"insert futures_contract " +
			"SET ref_number = ?, " +
			"transaction_date = ?, " +
			"bot_sold = ?, " +
			"tons = ?, " +
			"lots = ?, " +
			"hedge_price = ?, " +
			"differential = ?, " +
			"price = ?, " +
			"fixation_id = ?, " +
			"terminal_month = ?, " +
			"terminal_market = ?, " +
			"broker_id = ?, " +		
			"order_type = ?, " +	
			"quality_id = ?";
	  		
			Vector vp = new Vector();
			vp.addElement(new StringValue(RefNumber));
			vp.addElement(new DateValue(TransactionDate));
			vp.addElement(new StringValue(FType));
			vp.addElement(new DoubleValue(Tons));
			vp.addElement(new FloatValue(Lots));
			vp.addElement(new FloatValue(HedgePrice));
			vp.addElement(new FloatValue(Differential));
			vp.addElement(new FloatValue(Price));
			vp.addElement(new LongValue(FixationId));
			vp.addElement(new DateValue(TerminalMth));
			vp.addElement(new StringValue(TerminalMarket));
			vp.addElement(new IntValue(BrokerId));
			vp.addElement(new IntValue(OrdType));
			if (TerminalMarket.compareTo("LIFFE")==0)
			{
				vp.addElement(new IntValue(2));
			}
			else
			{
				vp.addElement(new IntValue(1));
			}
			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
	  	}
	  	else
	  	{
	  		System.out.println("Update FC");
	  		SqlStr =
			"update futures_contract " +
			"SET ref_number = ?, " +
			"transaction_date = ?, " +
			"bot_sold = ?, " +
			"tons = ?, " +
			"lots = ?, " +
			"hedge_price = ?, " +
			"differential = ?, " +
			"price = ?, " +
			"fixation_id = ?, " +
			"terminal_month = ?, " +
			"terminal_market = ?, " +
			"quality_id = ?, " +
			"order_type = ?, " +
			"broker_id = ? " +
			"where fixation_id = ?";
	  		
			Vector vp = new Vector();
			vp.addElement(new StringValue(RefNumber));
			vp.addElement(new DateValue(TransactionDate));
			vp.addElement(new StringValue(FType));
			vp.addElement(new DoubleValue(Tons));
			vp.addElement(new FloatValue(Lots));
			vp.addElement(new FloatValue(HedgePrice));
			vp.addElement(new FloatValue(Differential));
			vp.addElement(new FloatValue(Price));
			vp.addElement(new LongValue(FixationId));
			vp.addElement(new DateValue(TerminalMth));
			vp.addElement(new StringValue(TerminalMarket));
			if (TerminalMarket.compareTo("LIFFE")==0)
			{
				vp.addElement(new IntValue(2));
			}
			else
			{
				vp.addElement(new IntValue(1));
			}
			vp.addElement(new IntValue(OrdType));
			vp.addElement(new IntValue(BrokerId));			
			vp.addElement(new LongValue(id)) ;
			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
	  	}
		
		
	  }	  

	  
	  private void deleteFuturesContractRecord(long id)
	  {
	  	System.out.println("delete from futures_contract where fixation_id = " + id);
	  	getSQLCommand().executeUpdate("delete from futures_contract where fixation_id = " + id);
	  }
	  
	  private void updateRollContract(long id)
	  {
	  	int RollId = getRequest().getIntValue("roll_id");
	  	java.sql.Date RollDt = Utils.strToDate(getRequest().getStringValue("rolling_date"));
	  	java.sql.Date RollTerMth = Utils.strToDate(getRequest().getStringValue("roll_terminal_month"));
	  	float LotsRolled = getRequest().getFloatValue("lots_rolled");
	  	float TonsRolled = getRequest().getFloatValue("tons_rolled");
	  	float NewDiff = getRequest().getFloatValue("new_diff");
	  	
	  	java.sql.Date TerMth = Utils.strToDate(getRequest().getStringValue("ter_month"));
	  	float Diff = getRequest().getFloatValue("cnt_differential");

	  	long RecExist = findID("contract_roll_over","id",RollId);
	  	if(RecExist==0)
	  	{
	  	String SqlStr = "";
	  		SqlStr =
			"insert contract_roll_over " +
			"SET rolling_date = ?, " +
			"contract_id = ?, " +
			"tons_rolled = ?, " +
			"lots_rolled = ?, " +
			"old_diff = ?, " +
			"new_diff = ?, " +
			"old_terminal_month = ?, " +
			"new_terminal_month = ?";

			Vector vp = new Vector();
			vp.addElement(new DateValue(RollDt));
			vp.addElement(new LongValue(id));
			vp.addElement(new FloatValue(TonsRolled));
			vp.addElement(new FloatValue(LotsRolled));
			vp.addElement(new FloatValue(Diff));
			vp.addElement(new FloatValue(NewDiff));
			vp.addElement(new DateValue(TerMth));
			vp.addElement(new DateValue(RollTerMth));
			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
			
			SqlStr = "Update contracts set terminal_month = ?, " +
			         "differential = ? " +
					 "where id = ?";
			vp = new Vector();
			vp.addElement(new DateValue(RollTerMth));
			vp.addElement(new FloatValue(NewDiff));
			vp.addElement(new LongValue(id));
			ret = getSQLCommand().executeUpdate(SqlStr,vp);
	  	}
	  	else
	  	{
		  	String SqlStr = "";
	  		SqlStr =
			"update contract_roll_over " +
			"SET rolling_date = ?, " +
			"contract_id = ?, " +
			"tons_rolled = ?, " +
			"lots_rolled = ?, " +
			"old_diff = ?, " +
			"new_diff = ?, " +
			"old_terminal_month = ?, " +
			"new_terminal_month = ? " +
			"where id = ?";

			Vector vp = new Vector();
			vp.addElement(new DateValue(RollDt));
			vp.addElement(new LongValue(id));
			vp.addElement(new FloatValue(TonsRolled));
			vp.addElement(new FloatValue(LotsRolled));
			vp.addElement(new FloatValue(Diff));
			vp.addElement(new FloatValue(NewDiff));
			vp.addElement(new DateValue(TerMth));
			vp.addElement(new DateValue(RollTerMth));
			vp.addElement(new LongValue(RollId));
			int ret = getSQLCommand().executeUpdate(SqlStr,vp);
			
			long LatestId = getLatestID("contract_roll_over",RollId);
			
			if (LatestId == RollId)
			{
				SqlStr = "Update contracts set terminal_month = ?, " +
			         "differential = ? " +
					 "where id = ?";
				vp = new Vector();
				vp.addElement(new DateValue(RollTerMth));
				vp.addElement(new FloatValue(NewDiff));
				vp.addElement(new LongValue(id));
				ret = getSQLCommand().executeUpdate(SqlStr,vp);
			}
	  	}
	  }
 
}




class PrintSalFixContract extends GenReport
{
	 private Contract sales = null;
	 
	  public PrintSalFixContract(Tasks owner)
	  {
	    super(owner,"SalesCntFixation.xls");
	    this.sales = (Contract)owner;
	  }
	  
	  public String generate()
	  {
	  	long PcntId=sales.getRequest().getLongValue("contracts");
	  	ResultSet Rs = null;
	  	Rs = getSQLCommand().executeQuery("select * from contracts where id = " + PcntId);
  	
	  	try {
		  	Rs.beforeFirst();
		  	Rs.next();
		  	String Quality = sales.getFieldValueForId("quality_master","name",Rs.getInt("quality_id"));
		  	
			this.set_CellValue(4,7,Rs.getString("ref_number"));
			
			this.set_CellValue(4,9,Utils.dateToStr(Rs.getDate("contract_date")));			

		  	this.set_CellValue(4,11,sales.getFieldValueForId("company_master","name",Rs.getInt("supplier_buyer_id")));
			
			this.set_CellValue(4,13,Utils.dateToStr(Rs.getDate("terminal_month")).substring(3));
			
			this.set_CellValue(4,15,Utils.numberToStr(Rs.getFloat("differential"),2)) ;			
			
			this.set_CellValue(4,17,"US $ " + Utils.numberToStr(Rs.getFloat("contract_price_foreign"),2)) ;
			
			this.set_CellValue(4,19,Utils.numberToStr(Rs.getFloat("tons"),3)+ " Tons.") ;
			
			this.set_CellValue(4,21,Utils.numberToStr(Rs.getFloat("fixed_tons"),3)+ " Tons.") ;
			
			this.set_CellValue(4,23,Utils.numberToStr(Rs.getFloat("unfixed_tons"),3)+ " Tons.") ;			
			
			long Lot = 0;
			
			if (Quality.compareTo("Robusta")==0)
			{
				Lot = Math.round(Rs.getFloat("fixed_tons")/5);
			}
			else
			{
				Lot = Math.round(Rs.getFloat("fixed_tons")/17.01);
			}
			this.set_CellValue(4,25,Lot) ;
			
			if (Quality.compareTo("Robusta")==0)
			{
				Lot  = Math.round(Rs.getFloat("unfixed_tons")/10);
			}
			else
			{
				Lot  = Math.round(Rs.getFloat("unfixed_tons")/17.01);
			}
			this.set_CellValue(4,27,Lot) ;
			String CntType = Rs.getString("type");
			Rs.close();
			
			Rs = getSQLCommand().executeQuery("select * from contract_price_fixation where contract_id = " + PcntId + " Order by fixation_date");
			Rs.beforeFirst();
			
			int Loop = 30;
		  	HSSFRow TR = this.getRow((int)30);
		  	HSSFCell TC = TR.getCell((short)0);
		  	HSSFRow R = this.getRow((int)30);
		  	HSSFCell C = R.getCell((short)0);			
			while (Rs.next())
			{
				if (Loop == 30)
				{	
					this.set_CellValue(1,Loop,Utils.dateToStr(Rs.getDate("fixation_date")));
					this.set_CellValue(2,Loop,Utils.numberToStr(Rs.getFloat("lots_fixed"),0));
					this.set_CellValue(3,Loop," ");	   
					this.set_CellValue(4,Loop,Utils.numberToStr(Rs.getFloat("tons_fixed"),3));
					this.set_CellValue(5,Loop,Utils.numberToStr(Rs.getFloat("hedge_price"),2));
					this.set_CellValue(6,Loop,Utils.numberToStr(Rs.getFloat("price_foreign"),2));
					this.set_CellValue(7,Loop,Utils.dateToStr(Rs.getDate("terminal_month")).substring(3));
				}
				else
				{
					this.set_CellValue(1,Loop,Utils.dateToStr(Rs.getDate("fixation_date")));
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)1);
					C = R.getCell((short)1);
	    			C.setCellStyle(TC.getCellStyle());
	    			
					this.set_CellValue(2,Loop,Utils.numberToStr(Rs.getFloat("lots_fixed"),0));
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)2);
					C = R.getCell((short)2);
	    			C.setCellStyle(TC.getCellStyle());
	    			
	    			this.set_CellValue(3,Loop," ");	    			
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)3);
					C = R.getCell((short)3);
	    			C.setCellStyle(TC.getCellStyle());	    			
	    			
					this.set_CellValue(4,Loop,Utils.numberToStr(Rs.getFloat("tons_fixed"),3));
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)4);
					C = R.getCell((short)4);
	    			C.setCellStyle(TC.getCellStyle());
	    			
					this.set_CellValue(5,Loop,Utils.numberToStr(Rs.getFloat("hedge_price"),2));
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)5);
					C = R.getCell((short)5);
	    			C.setCellStyle(TC.getCellStyle());
	    			
					this.set_CellValue(6,Loop,Utils.numberToStr(Rs.getFloat("price_foreign"),2));
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)6);
					C = R.getCell((short)6);
	    			C.setCellStyle(TC.getCellStyle());
	    			
					this.set_CellValue(7,Loop,Utils.dateToStr(Rs.getDate("terminal_month")).substring(3));
					R=this.getRow((int)Loop);
					TC = TR.getCell((short)7);
					C = R.getCell((short)7);
	    			C.setCellStyle(TC.getCellStyle());	    			
				}
				Loop=Loop+1;
			}
			Loop= Loop+4;
			
			this.set_CellValue(0,Loop,"Accepted");
			
			Loop=Loop+4;
			
	  		this.set_CellValue(0,Loop,"Seller : ______________________");
	  		this.set_CellValue(5,Loop,"Buyer : ______________________");
	  		
	  		Loop=Loop+1;
	  		if ((sales.getUser().getComanyName().toUpperCase()).compareTo("MERCON COFFEE CORP.")==0)
	  		{
	  			if (CntType.compareTo("P")==0) this.set_CellValue(5,Loop,"Mercon Coffee Corporation");
	  			if (CntType.compareTo("S")==0) this.set_CellValue(0,Loop,"Mercon Coffee Corporation");
	  		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	return export();	
	  }

}

class PrintMercafePriceConfirmation extends GenReport
{
	 private Contract purchase = null;
	 
	  public PrintMercafePriceConfirmation(Tasks owner)
	  {
	    super(owner,"Mercafe_PriceConfirmation.xls");
	    this.purchase = (Contract)owner;
	  }
	  
	  public String generate()
	  {
	  	long PcntId=purchase.getRequest().getLongValue("contracts");
	  	ResultSet Rs = null;
	  	Rs = getSQLCommand().executeQuery("select * from contracts where id = " + PcntId);
  	
	  	try {
		  	Rs.beforeFirst();
		  	Rs.next();
		  	String Quality = purchase.getFieldValueForId("quality_master","name",Rs.getInt("quality_id"));
		  	String Grade = purchase.getFieldValueForId("grade_master","name",Rs.getInt("grade_id"));
		  	
			this.set_CellValue(0,5,Utils.today());
			
			this.set_CellValue(0,7,purchase.getFieldValueForId("company_master","name",Rs.getInt("supplier_buyer_id")));
			this.set_CellValue(0,8,purchase.getFieldValueForId("company_master","address",Rs.getInt("supplier_buyer_id")));
			
			this.set_CellValue(5,16,Rs.getString("ref_number"));

			this.set_CellValue(5,18,Utils.numberToStr(Rs.getFloat("no_of_lots"),0,false) + " Lots (" + Utils.numberToStr(Rs.getFloat("no_of_bags"),0,false) + " Bags). " + Quality + " " + Grade);
			
			this.set_CellValue(0,21,"We have priced " + Utils.numberToStr(Rs.getFloat("no_of_lots"),0,false) + " Lots as follows.");
			
			this.set_CellValue(5,24,Utils.numberToStr(Rs.getFloat("hedge_price"),2,false));
			
			this.set_CellValue(5,26,Utils.numberToStr(Rs.getFloat("differential"),2,false));
			
			this.set_CellValue(5,28,Utils.numberToStr(Rs.getFloat("contract_price_foreign"),2,false));
			
			//this.getCell(5,32).setCellFormula("=F28-F30");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	return export();	
	  }

}

class PrintVinacofPriceConfirmation extends GenReport
{
	 private Contract purchase = null;
	 
	  public PrintVinacofPriceConfirmation(Tasks owner)
	  {
	    super(owner,"Vinacof Confirmation Contract.xls");
	    this.purchase = (Contract)owner;
	  }
	  
	  public String generate()
	  {
	  	long PcntId=purchase.getRequest().getLongValue("contracts");
	  	ResultSet Rs = null;
	  	Rs = getSQLCommand().executeQuery("select * from contracts where id = " + PcntId);
  	
	  	try {
		  	Rs.beforeFirst();
		  	Rs.next();
		  	String Quality = purchase.getFieldValueForId("quality_master","name",Rs.getInt("quality_id"));
		  	String Grade = purchase.getFieldValueForId("grade_master","name",Rs.getInt("grade_id"));
		  	
			this.set_CellValue(2,11,Utils.today());
			
			this.set_CellValue(2,12,purchase.getFieldValueForId("company_master","name",Rs.getInt("supplier_buyer_id")));
			
			this.set_CellValue(2,14,Rs.getString("ref_number"));

			this.set_CellValue(2,16,Utils.numberToStr(Rs.getFloat("tons"),3,false));
			
			this.set_CellValue(2,17,Quality + " - " + Grade);
			
			float Price = (Rs.getFloat("contract_price_foreign")/1000);
			this.set_CellValue(2,18,Utils.numberToStr(Price,2,false));
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	return export();	
	  }

}

class PrintMercafeAmendedContract extends GenReport
{
	 private Contract purchase = null;
	 
	  public PrintMercafeAmendedContract(Tasks owner)
	  {
	    super(owner,"Mercafe_Amended_Contract.xls");
	    this.purchase = (Contract)owner;
	  }
	  
	  public String generate()
	  {
	  	long PcntId=purchase.getRequest().getLongValue("contracts");
	  	long RollId=purchase.getRequest().getLongValue("roll_id");
	  	ResultSet Rs = null;
	  	String Sql = "SELECT  contract_roll_over.id,contract_roll_over.rolling_date, " +
					 "contract_roll_over.contract_id,contract_roll_over.tons_rolled, " +
					 "contract_roll_over.lots_rolled,contract_roll_over.old_diff, " +
					 "contract_roll_over.new_diff,contract_roll_over.old_terminal_month, " +
					 "contract_roll_over.new_terminal_month,contracts.contract_date, " +
					 "contracts.unfixed_tons,contracts.contract_no, " +
					 "contracts.supplier_buyer_id,contracts.price_fix_option,contracts.terminal_market " +
					 "FROM contracts " +
					 "INNER JOIN contract_roll_over ON (contracts.id=contract_roll_over.contract_id) " +
					 "AND contract_roll_over.id = " + RollId;
	  	Rs = getSQLCommand().executeQuery(Sql);
  	
	  	try {
		  	Rs.beforeFirst();
		  	Rs.next();
	  	
			this.set_CellValue(0,12,Rs.getDate("rolling_date"));
			this.set_CellValue(7,12,Rs.getString("contract_no"));
			
			this.set_CellValue(0,15,purchase.getFieldValueForId("company_master","name",Rs.getInt("supplier_buyer_id")));
			//this.set_CellValue(0,16,purchase.getFieldValueForId("company_master","address",Rs.getInt("supplier_buyer_id")));
			
			String OptStr="";
			if (Rs.getString("price_fix_option").compareTo("B")==0)
			{
				OptStr = "At Buyer Call.";
			}
			else
			{
				OptStr = "At Seller Call.";
			}
			
			float DiffVal = Rs.getFloat("new_diff");
			String DiffStr ="";
			if (DiffVal > 0)
			{
				DiffStr = "Plus";
			}
			else
			{
				DiffStr = "Minus";
			}	
			
			String Price ="";
			Price = "To be fixed at " + OptStr + " against " + 
			        Rs.getString("terminal_market") + " " + 
					Utils.dateToStr(Rs.getDate("new_terminal_month")).substring(3) +
					" at " + DiffStr + 
					" " + Utils.numberToStr(Rs.getFloat("new_diff"),0,false) + 
					" (" + Utils.numberToStr(Rs.getFloat("lots_rolled"),0,false) +
					").";
			
			this.set_CellValue(1,23,Price);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	return export();	
	  }	  
}